package com.gsmr.controller;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gsmr.util.*;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.gsmr.entity.PageBean;
import com.gsmr.entity.SACCH;
import com.gsmr.service.InterferenceQueryService;
import com.gsmr.service.SACCHService;
import com.gsmr.service.TCHService;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * SACCH Controller层
 * @author user
 *
 */
@Controller
@RequestMapping("/sacch")
public class SACCHController {
	@Resource
	private TCHService tchService;

	@Resource
	private SACCHService sacchService;
	
	@Resource
	private InterferenceQueryService interferencequeryService;
	
	List<SACCH> sacchList;
	
	/**
	 * 分页条件查询SACCH
	 */
	@RequestMapping("/list")
	public String list(@RequestParam(value="page",required=false)String page, @RequestParam(value="rows",required=false)String rows, @RequestParam(value="startTime",required=false)String startTime,
			@RequestParam(value="endTime",required=false)String endTime, @RequestParam(value="IMSI",required=false)String IMSI, @RequestParam(value="MSISDN",required=false)String MSISDN,
			@RequestParam(value="CTCS_ID",required=false)String CTCS_ID, @RequestParam(value="UmType",required=false)String UmType, @RequestParam(value="district",required=false)String district,
			@RequestParam(value="kilometerStart",required=false)String kilometerStart, @RequestParam(value="kilometerEnd",required=false)String kilometerEnd,
			@RequestParam(value = "search", required = false) String search, @RequestParam(value = "increase", required = false) String increase, 
			@RequestParam(value = "recordId", required = false) String recordId, SACCH sacch,HttpServletResponse response) throws Exception{
		if (search == null)
			return null;

		PageBean pageBean=new PageBean(Integer.parseInt(page),Integer.parseInt(rows));
		Map<String,Object> map=new HashMap<String,Object>();
		String IMSI1 = "1";
		String IMSI2 = "1";
		String MSISDN1 = "1";
		String MSISDN2 = "1";
		List<String> traindata = new ArrayList<>();
		map.put("table_name", "train_info");
		if(!IMSI.equals("")) {
			IMSI1 = IMSI;
			map.put("IMSI", IMSI1);
			traindata = tchService.get_msisdn(map);
			if(traindata.size() != 0) {
				MSISDN1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!MSISDN.equals("")) {
			MSISDN1 = MSISDN;
			map.put("MSISDN", MSISDN1);
			traindata = tchService.get_imsi(map);
			if(traindata.size() != 0) {
				IMSI1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!CTCS_ID.equals("")) {
			map.put("CTCS_ID", CTCS_ID);
			traindata = tchService.get_imsi(map);
			if(traindata.size() >= 2) {
				IMSI1 = traindata.get(0);
				IMSI2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				IMSI1 = traindata.get(0);
			}
			traindata = tchService.get_msisdn(map);
			if(traindata.size() >= 2) {
				MSISDN1 = traindata.get(0);
				MSISDN2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				MSISDN1 = traindata.get(0);
			}
		}
		map.put("IMSI1", IMSI1);
		map.put("IMSI2", IMSI2);
		map.put("MSISDN1", MSISDN1);
		map.put("MSISDN2", MSISDN2);
		map.put("CTCS_ID", CTCS_ID);
		map.put("UmTypeAll", UmType);
		map.put("districtAll", district);
		map.put("kilometerStart", kilometerStart);
		map.put("kilometerEnd", kilometerEnd);
		map.put("recordId", recordId);

		if(UmType!=null&&!UmType.equals("")){
			String[] subUmType=UmType.split(",");
			List<String> subList=Arrays.asList(subUmType);
			map.put("UmType", subList);
		} else {
			map.put("UmType", "");
		}

		System.out.println("测量报告开始查询3");
		if(district!=null&&!district.equals("")){
			String[] subdistrict=district.split(",");
			String[] frontID=new String[subdistrict.length];
			for(int i=0;i<subdistrict.length;i++) {
				if(subdistrict[i].equals(""))
					continue;
				map.put("frontId", subdistrict[i]);
				map.put("table_name","bs_info");
				frontID[i]=interferencequeryService.get_frontId(map);
			}
			List<String> subList=Arrays.asList(frontID);
			System.out.println("测量报告id查询： " + subList);
			map.put("district", subList);
		}
		
		List<String> table_names = setupTimeForMap(startTime, endTime, map);
		
		sacchList = new ArrayList<SACCH>();

		if (table_names != null && table_names.size() > 0) {
			map.put("start", 0);
			map.put("size", pageBean.getPageSize());
			System.out.println("测量报告设置页码：" + map);
			System.out.println("测量报告increase = " + increase);
			if(increase.equals("1")) {

				for (int i = table_names.size() - 1; i >= 0 && sacchList.size() < pageBean.getPageSize(); i--) {
					map.put("table_name", table_names.get(i));

					System.out.println("测量报告表名查询：" + map);
					List<SACCH> sub_sacchList = sacchService.findASC(map);

					System.out.println("测量报告查询到" + sub_sacchList.size() + "条数据");
					if (sub_sacchList != null && sub_sacchList.size() > 0) sacchList.addAll(sub_sacchList);
				}
			} else {
				for (int i = 0; i < table_names.size() && sacchList.size() < pageBean.getPageSize(); i++) {

					map.put("table_name", table_names.get(i));

					System.out.println("测量报告表名查询：" + map);
					List<SACCH> sub_sacchList = sacchService.find(map);
					System.out.println("测量报告查询到" + sub_sacchList.size() + "条数据");
					if (sub_sacchList != null && sub_sacchList.size() > 0) sacchList.addAll(sub_sacchList);
				}
			}
		}
		//新增处理SNR字段转换成-->10log（SNR）
		if (sacchList.size()>0) {
			for (SACCH tmpsacch : sacchList) {
				if(StringUtil.isNotEmpty(tmpsacch.getSNR())) {
					StringBuffer SNR=new StringBuffer();
					String[] SNRArry = tmpsacch.getSNR().split(" ");
					for (int i = 0; i < SNRArry.length; i++) {
						{
							double d = Double.parseDouble(SNRArry[i].trim());
							if(d>0) {
//						d=(Math.log10(d))*10;
								d = (double) Math.round(((Math.log10(d)) * 10) * 10) / 10;
								SNRArry[i] = String.valueOf((d));
//								System.out.println("SNRArry[" + i + "]:=================" + SNRArry[i]);
								SNR.append(SNRArry[i] + " ");
							}else {
								SNR.append("/ ");
							}
						}
//						System.out.println(SNR);
						tmpsacch.setSNR(String.valueOf(SNR));
					}
				}
			}
		}
		SACCHController.ListSort(sacchList);
		if(sacchList.size()>pageBean.getPageSize()&&increase.equals("1")) {
			sacchList=sacchList.subList(0, pageBean.getPageSize());
		} else if (sacchList.size()>pageBean.getPageSize()&&increase.equals("0")) {
			sacchList=sacchList.subList(sacchList.size()-pageBean.getPageSize(), sacchList.size());
		}
		
		if (sacchList.size() < pageBean.getPageSize() && increase.equals("0") && sacchList.size() > 0) {
//			String tmptime1 = sacchList.get(0).getTimeStamp();
            String tmptime1= sacchList.get(0).getStartTime();
            System.out.println("触发时间："+tmptime1);
	    	map.put("endTime", null);
			map.put("startTime", DateUtil.longToString(Long.parseLong(tmptime1),"yyyyMMdd"));
            System.out.println(map.get(startTime));
			table_names = sacchService.findTableNames(map);
			map.put("endTime", tmptime1);
			sacchList.clear();
			if (table_names != null && table_names.size() > 0) {
				map.put("start", 0);
				map.put("size", pageBean.getPageSize());
				for (int i = table_names.size() - 1; i >= 0 && sacchList.size() < pageBean.getPageSize(); i--) {
					map.put("table_name", table_names.get(i));
					List<SACCH> sub_sacchList = sacchService.findASC(map);
					if (sub_sacchList != null && sub_sacchList.size() > 0)
						sacchList.addAll(sub_sacchList);
				}
			}
			SACCHController.ListSort(sacchList);
			if (sacchList.size() > pageBean.getPageSize()) {
				sacchList = sacchList.subList(0, pageBean.getPageSize());
			}
	    }
//        Iterator<SACCH> iterator=sacchList.iterator();
//		SACCH sacch2=new SACCH();
//		while (iterator.hasNext()){
//
//          sacch2=iterator.next();
//            System.out.println("timeStamp="+sacch2.getTimeStamp());
//            System.out.println("StartTime = " + sacch2.getStartTime());
//
//        }
		JSONObject result=new JSONObject();
		//此处StartTime字段转化为jsonArray时key值首字母转为小写
		JSONArray jsonArray=JSONArray.fromObject(sacchList);
        System.out.println(jsonArray);
		result.put("rows", jsonArray);
		result.put("total", Integer.MAX_VALUE);
		ResponseUtil.write(response, result);
		return null;
	}
	
	/**
	 * 设置开始时间和结束时间
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @param map 查询条件
	 * @return 这段时间内对应数据库表名
	 */
	private List<String> setupTimeForMap(String startTime, String endTime, Map<String,Object> map) {
		//当天00:00的时间戳
		Long earlyMorningTime = System.currentTimeMillis()/(1000*3600*24)*(1000*3600*24)-TimeZone.getDefault().getRawOffset();
		//当前时间戳
		Long nowTime = System.currentTimeMillis();
		if(startTime!=null&&!startTime.equals("NaN")){
			map.put("startTime", DateUtil.longToString(Long.parseLong(startTime),"yyyyMMdd"));
		} else {
			 map.put("startTime", DateUtil.longToString(earlyMorningTime,"yyyyMMdd"));
		}
		if(endTime!=null&&!endTime.equals("NaN")){
			map.put("endTime", DateUtil.longToString(Long.parseLong(endTime),"yyyyMMdd"));
		} else {
			map.put("endTime", DateUtil.longToString(nowTime,"yyyyMMdd"));
		}
		
		List<String> table_names = sacchService.findTableNames(map);
		System.out.println("查询表名：" + table_names);
		if (startTime != null && !startTime.equals("NaN")) {
			map.put("startTime", startTime);
		} else {
			 map.put("startTime", null);
		}
		if(endTime!=null&&!endTime.equals("NaN")){
			map.put("endTime", endTime);
		} else {
			map.put("endTime", null);
		}
		return table_names;
	}
	
	String headers[] = { "触发时间", "IMSI", "MSISDN", "机车号", "小区名称", "BCCH", "TCH", "时隙号", "方向", "帧号", "Um接口消息类型", "RX_LEV_Full", "RX_QUAL_Full", "TA", "ARFCNs", 
			"FP_1000", "FP_1001", "FP_1002", "FP_1003", "FP_1004", "FP_1005", "FP_1006", "FP_1007", "FP_1008", "FP_1009", "FP_1010", "FP_1011", "FP_1012", "FP_1013", "FP_1014", 
			"FP_1015", "FP_1016", "FP_1017", "FP_1018", "公里标", "速度（km/h）", "数据长度", "LEVEL", "SNR", "QUALITY", "数据源码", "解码信息", "终端类型", "TMSI", "LAC", "CI", "前置机号", "记录号" };
	
	/**
	 * 导出SACCH
	 */
	@RequestMapping("/export")
	public String export(@RequestParam(value = "startTime", required = false) String startTime, @RequestParam(value = "endTime", required = false) String endTime,
			@RequestParam(value = "IMSI", required = false) String IMSI, @RequestParam(value = "MSISDN", required = false) String MSISDN,
			@RequestParam(value = "CTCS_ID", required = false) String CTCS_ID, @RequestParam(value = "UmType", required = false) String UmType,
			@RequestParam(value = "district", required = false) String district, @RequestParam(value = "kilometerStart", required = false) String kilometerStart,
			@RequestParam(value = "kilometerEnd", required = false) String kilometerEnd, @RequestParam(value = "recordId", required = false) String recordId, HttpServletResponse response)throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();

		String IMSI1 = "1";
		String IMSI2 = "1";
		String MSISDN1 = "1";
		String MSISDN2 = "1";
		List<String> traindata = new ArrayList<>();
		map.put("table_name", "train_info");
		if(!IMSI.equals("")) {
			IMSI1 = IMSI;
			map.put("IMSI", IMSI1);
			traindata = tchService.get_msisdn(map);
			if(traindata.size() != 0) {
				MSISDN1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!MSISDN.equals("")) {
			MSISDN1 = MSISDN;
			map.put("MSISDN", MSISDN1);
			traindata = tchService.get_imsi(map);
			if(traindata.size() != 0) {
				IMSI1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!CTCS_ID.equals("")) {
			map.put("CTCS_ID", CTCS_ID);
			traindata = tchService.get_imsi(map);
			if(traindata.size() >= 2) {
				IMSI1 = traindata.get(0);
				IMSI2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				IMSI1 = traindata.get(0);
			}
			traindata = tchService.get_msisdn(map);
			if(traindata.size() >= 2) {
				MSISDN1 = traindata.get(0);
				MSISDN2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				MSISDN1 = traindata.get(0);
			}
		}
		map.put("IMSI1", IMSI1);
		map.put("IMSI2", IMSI2);
		map.put("MSISDN1", MSISDN1);
		map.put("MSISDN2", MSISDN2);
		map.put("CTCS_ID", CTCS_ID);
		map.put("UmTypeAll", UmType);
		map.put("districtAll", district);
		map.put("kilometerStart", kilometerStart);
		map.put("kilometerEnd", kilometerEnd);
		map.put("recordId", recordId);

		if (UmType != null && !UmType.equals("")) {
			String[] subUmType = UmType.split(",");
			List<String> subList = Arrays.asList(subUmType);
			map.put("UmType", subList);
		} else {
			map.put("UmType", "");
		}
		if (district != null && !district.equals("")) {
			String[] subdistrict = district.split(",");
			String[] frontID = new String[subdistrict.length];
			for (int i = 0; i < subdistrict.length; i++) {
				if (subdistrict[i].equals(""))
					continue;
				map.put("frontId", subdistrict[i]);
				map.put("table_name", "bs_info");
				frontID[i] = interferencequeryService.get_frontId(map);
			}
			List<String> subList = Arrays.asList(frontID);
			map.put("district", subList);
		}
		
		List<String> table_names = setupTimeForMap(startTime, endTime, map);

		List<SACCH> sacchList_all = new ArrayList<SACCH>();
		List<SACCH> sub_sacchList = null;
		if(table_names!=null){
			map.put("start", 0);
			for (int i = table_names.size() - 1; i >= 0 && sacchList_all.size() <= ExcelUtil.maxExportRows; i--) {
				sub_sacchList = null;
				map.put("size", ExcelUtil.maxExportRows - sacchList_all.size());
				map.put("table_name", table_names.get(i));
				sub_sacchList = sacchService.findASC(map);
				if(sub_sacchList != null && sub_sacchList.size() > 0){
					sacchList_all.addAll(sub_sacchList);
				}
			}
		}
		if (sacchList_all.size()>0) {
			for (SACCH tmpsacch : sacchList_all) {
				if(StringUtil.isNotEmpty(tmpsacch.getSNR())) {
					StringBuffer SNR=new StringBuffer();
					String[] SNRArry = tmpsacch.getSNR().split(" ");
					for (int i = 0; i < SNRArry.length; i++) {
						{
							double d = Double.parseDouble(SNRArry[i].trim());
							if(d>0) {
//						d=(Math.log10(d))*10;
								d = (double) Math.round(((Math.log10(d)) * 10) * 10) / 10;
								SNRArry[i] = String.valueOf((d));
//								System.out.println("SNRArry[" + i + "]:=================" + SNRArry[i]);
								SNR.append(SNRArry[i] + " ");
							}else {
								SNR.append("/ ");
							}
						}
//						System.out.println(SNR);
						tmpsacch.setSNR(String.valueOf(SNR));
					}
				}
			}
		}
		SACCHController.ListSort(sacchList_all);
		//List<SACCH> sub_sacchList_all=sacchList_all;
		if (sacchList_all.size() > ExcelUtil.maxExportRows) {
			sacchList_all = sacchList_all.subList(0, ExcelUtil.maxExportRows);
		}
		Workbook wb = new HSSFWorkbook();
		ExcelUtil.fillExcelData_SACCH(sacchList_all, wb, headers);
		ResponseUtil.export(response, wb, "测量报告及系统消息" + FormatDate.formatDate() + ".xls");
		return null;
	}
	
	/**
	 * 导出当前页的数据
	 */
	@RequestMapping("/exportcurrentpage")
	public String exportcurrentpage(@RequestParam(value = "data", required = false) String data, HttpServletResponse response) throws Exception {

		if (data == null || data.length() < 5) return null;

		Workbook wb = new HSSFWorkbook();

		JSONArray jsonArray = JSONArray.fromObject(data);
		List<SACCH> sacchList = (List<SACCH>) JSONArray.toCollection(jsonArray, SACCH.class);
		SACCHController.ListSort(sacchList);
		ExcelUtil.fillExcelData_SACCH(sacchList, wb, headers);
		ResponseUtil.export(response, wb, "测量报告及系统消息" + FormatDate.formatDate() + ".xls");
		return null;
	}
	
	/**
	 * 判断是否存在指定PrimaryKey
	 */
	@RequestMapping("/exisPrimaryKey")
	public String existUserName(String primaryKey,HttpServletResponse response)throws Exception{
		JSONObject result=new JSONObject();
		Map<String,Object> map=new HashMap<String,Object>();
		String frontProcessorId = primaryKey.substring(0,primaryKey.indexOf("-"));
		map.put("table_name", "sacch_"+frontProcessorId);
		map.put("PrimaryKey", primaryKey);
		if(sacchService.findByPrimaryKey(map)==null){
			result.put("exist", false);
		}else{
			result.put("exist", true);
		}
		ResponseUtil.write(response, result);
		return null;
	}
	
	/**
	 * 添加或者修改SACCH
	 * @param
	 * @param response
	 * @param flag 1 添加  2修改
	 */
	@RequestMapping("/save")
	public String save(SACCH sacch,HttpServletResponse response,Integer flag)throws Exception{

		if (sacch == null)
			return null;

		int resultTotal=0; // 操作的记录条数
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("table_name", "sacch_"+sacch.getFrontProcessorId());

		// map = new BeanMap(sacch);

		map.put("PrimaryKey", sacch.getPrimaryKey());
		map.put("TimeStamp", sacch.getTimeStamp());
		map.put("StartTime", sacch.getStartTime());
		map.put("FrontProcessorId", sacch.getFrontProcessorId());
		map.put("RecordId", sacch.getRecordId());
		map.put("IMSI", sacch.getTMSI());
		map.put("LogicChannel", sacch.getLogicChannel());
		map.put("MSISDN", sacch.getMSISDN());
		map.put("Direction", sacch.getDirection());
		map.put("FrameNumber", sacch.getFrameNumber());
		map.put("SiteName", sacch.getSiteName());
		map.put("Decode", sacch.getDecode());
		map.put("RawMessage", sacch.getRawMessage());
		map.put("BCCH", sacch.getBCCH());
		map.put("NO_NCELL_M", sacch.getNO_NCELL_M());
		map.put("BCCH_FREQ_NCELL", sacch.getBCCH_FREQ_NCELL());
		map.put("Kilometer", sacch.getKilometer());
		map.put("Speed", sacch.getSpeed());
		map.put("CI", sacch.getCI());
		map.put("LAC", sacch.getLAC());
		map.put("CTCS_ID", sacch.getCTCS_ID());
		map.put("ENGINE_ID", sacch.getENGINE_ID());
		map.put("MS_TYPE", sacch.getMS_TYPE());
		map.put("Message_TYPE", sacch.getMessage_TYPE());
		map.put("SourceData", sacch.getSourceData());
		map.put("PowerLevel", sacch.getPowerLevel());
		map.put("RX_LEV_Full", sacch.getRX_LEV_Full());
		map.put("RX_LEV_Sub", sacch.getRX_LEV_Sub());
		map.put("RX_QUAL_Full", sacch.getRX_QUAL_Full());
		map.put("RX_QUAL_Sub", sacch.getRX_QUAL_Sub());
		map.put("TA", sacch.getTA());
		map.put("TS", sacch.getTS());
		map.put("ARFCNs", sacch.getARFCNs());
		
		map.put("FP_1000", sacch.getFP_1000());
		map.put("FP_1001", sacch.getFP_1001());
		map.put("FP_1002", sacch.getFP_1002());
		map.put("FP_1003", sacch.getFP_1003());
		map.put("FP_1004", sacch.getFP_1004());
		map.put("FP_1005", sacch.getFP_1005());
		map.put("FP_1006", sacch.getFP_1006());
		map.put("FP_1007", sacch.getFP_1007());
		map.put("FP_1008", sacch.getFP_1008());
		map.put("FP_1009", sacch.getFP_1009());
		map.put("FP_1010", sacch.getFP_1010());
		map.put("FP_1011", sacch.getFP_1011());
		map.put("FP_1012", sacch.getFP_1012());
		map.put("FP_1013", sacch.getFP_1013());
		map.put("FP_1014", sacch.getFP_1014());
		map.put("FP_1015", sacch.getFP_1015());
		map.put("FP_1016", sacch.getFP_1016());
		map.put("FP_1017", sacch.getFP_1017());
		map.put("FP_1018", sacch.getFP_1018());
		
		map.put("Frame", sacch.getFrame());
		map.put("TimeSlot", sacch.getTimeSlot());
		map.put("TMSI", sacch.getTMSI());
		map.put("Channel", sacch.getChannel());
		map.put("TCH", sacch.getTCH());
		map.put("IMEI", sacch.getIMEI());
		map.put("L2", sacch.getL2());
		map.put("DECODED", sacch.getDECODED());
		if(flag==1){
			resultTotal=sacchService.add(map);
		}else{
			resultTotal=sacchService.update(map);
		}
		JSONObject result=new JSONObject();
		if(resultTotal>0){
			result.put("success", true);
		}else{
			result.put("success", false);
		}
		ResponseUtil.write(response, result);
		return null;
	}
	
	/**
	 * 删除SACCH
	 */
	@RequestMapping("/delete")
	public String delete(@RequestParam(value="primaryKeys")String primaryKeys,HttpServletResponse response)throws Exception{
		String []primaryKeysStr=primaryKeys.split(",");
		Map<String,Object> map=new HashMap<String,Object>();
		JSONObject result=new JSONObject();
		try {
			for(int i=0;i<primaryKeysStr.length;i++){
				String frontProcessorId = primaryKeysStr[i].substring(0,primaryKeysStr[i].indexOf("-"));
				map.put("table_name", "sacch_"+frontProcessorId);
				map.put("PrimaryKey", primaryKeysStr[i]);
				sacchService.delete(map);
			}
			result.put("success", true);
		} catch (Exception e) {
			result.put("success", false);
			e.printStackTrace();
		}
		ResponseUtil.write(response, result);
		return null;
	}
	
	/**
	 * 用模板导出用户
	 */
	@RequestMapping("/exportByTemplate")
	public String exportByTemplate(@RequestParam(value="ids")String ids,HttpServletResponse response)throws Exception{
		Map<String,Object> map=new HashMap<String,Object>();
		List<SACCH> sacchList = new ArrayList<SACCH>();
		if(ids!=null&&!ids.equals("null")&&!ids.equals("")){
			List<String> idsList = Arrays.asList(ids.split(","));
			map.put("ids", idsList);
			sacchList.clear();
			List<SACCH> sub_sacchList = null;
			List<String> table_names = sacchService.findTableNames(map);
			if(table_names!=null){
				for (int i = 0; i < table_names.size(); i++) {
					sub_sacchList = null;
					map.put("table_name", table_names.get(i));
					sub_sacchList = sacchService.findByIds(map);
					if (sub_sacchList != null) {
						sacchList.addAll(sub_sacchList);
					}
				}
			}
			SACCHController.ListSort(sacchList);
			
			try {
				Workbook wb=ExcelUtil.fillExcelDataWithTemplate_SACCH(sacchList, "sacchExportTemplate.xls");
				ResponseUtil.export(response, wb, "利用模版导出excel"+FormatDate.formatDate()+".xls");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			sacchList.clear();
			List<SACCH> sub_sacchList = null;
			List<String> table_names = sacchService.findTableNames(map);
			if(table_names!=null){
				for(int i=0;i<table_names.size();i++){
					sub_sacchList = null;
					map.put("table_name", table_names.get(i));
					sub_sacchList = sacchService.find(map);
					sacchList.addAll(sub_sacchList);
				}
			}
			SACCHController.ListSort(sacchList);
			
			try {
				Workbook wb=ExcelUtil.fillExcelDataWithTemplate_SACCH(sacchList, "sacchExportTemplate.xls");
				ResponseUtil.export(response, wb, "利用模版导出excel"+FormatDate.formatDate()+".xls");
			} catch (Exception e) {
				e.printStackTrace();
			}		
		}
		return null;
	}
	
	/**
	 * 用户上传
	 */
	@RequestMapping("/upload")
	public String upload(HttpServletRequest request,HttpServletResponse response)throws Exception{

		JSONObject result=new JSONObject();
		Map<String,Object> map=new HashMap<String,Object>();
		 try{
        //将当前上下文初始化给  CommonsMutipartResolver （多部分解析器）
       CommonsMultipartResolver multipartResolver=new CommonsMultipartResolver(request.getSession().getServletContext());
       //检查form中是否有enctype="multipart/form-data"

       if(multipartResolver.isMultipart(request))
       {
           //将request变成多部分request
           MultipartHttpServletRequest multiRequest=(MultipartHttpServletRequest)request;
          //获取multiRequest 中所有的文件名
           Iterator iter=multiRequest.getFileNames();
           
           while(iter.hasNext())
           {
               //一次遍历所有文件
               MultipartFile file=multiRequest.getFile(iter.next().toString());
               if(file!=null)
               {
                   String path=PropertityUtil.UPLOAD_PATH+FormatDate.formatDate()+file.getOriginalFilename();
                   //上传
                   File newFile = new File(path);
                   file.transferTo(newFile);
                   //写数据进数据库
                   POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(newFile));
						HSSFWorkbook wb = new HSSFWorkbook(fs);
						HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
						if (hssfSheet != null) {
              				for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
              					HSSFRow hssfRow=hssfSheet.getRow(rowNum);
              					if(hssfRow==null){
              						continue;
              					}
              					SACCH sacch=new SACCH();
								sacch.setPrimaryKey(ExcelUtil.formatCell(hssfRow.getCell(0)));
								sacch.setTimeStamp(ExcelUtil.formatCell(hssfRow.getCell(1)));
								sacch.setStartTime(ExcelUtil.formatCell(hssfRow.getCell(2)));
								sacch.setFrontProcessorId(ExcelUtil.formatCell(hssfRow.getCell(3)));
								sacch.setRecordId(ExcelUtil.formatCell(hssfRow.getCell(4)));
								sacch.setIMSI(ExcelUtil.formatCell(hssfRow.getCell(5)));
								sacch.setLogicChannel(ExcelUtil.formatCell(hssfRow.getCell(6)));
								sacch.setMSISDN(ExcelUtil.formatCell(hssfRow.getCell(7)));
								sacch.setDirection(ExcelUtil.formatCell(hssfRow.getCell(8)));
								sacch.setFrameNumber(ExcelUtil.formatCell(hssfRow.getCell(9)));
								sacch.setSiteName(ExcelUtil.formatCell(hssfRow.getCell(10)));
								sacch.setDecode(ExcelUtil.formatCell(hssfRow.getCell(11)));
								sacch.setRawMessage(ExcelUtil.formatCell(hssfRow.getCell(12)));
								sacch.setBCCH(ExcelUtil.formatCell(hssfRow.getCell(13)));
								sacch.setNO_NCELL_M(ExcelUtil.formatCell(hssfRow.getCell(14)));
								sacch.setBCCH_FREQ_NCELL(ExcelUtil.formatCell(hssfRow.getCell(15)));
								sacch.setKilometer(ExcelUtil.formatCell(hssfRow.getCell(16)));
								sacch.setSpeed(ExcelUtil.formatCell(hssfRow.getCell(17)));
								sacch.setCI(ExcelUtil.formatCell(hssfRow.getCell(18)));
								sacch.setLAC(ExcelUtil.formatCell(hssfRow.getCell(19)));
								sacch.setCTCS_ID(ExcelUtil.formatCell(hssfRow.getCell(20)));
								sacch.setENGINE_ID(ExcelUtil.formatCell(hssfRow.getCell(21)));
								sacch.setMS_TYPE(ExcelUtil.formatCell(hssfRow.getCell(22)));
								sacch.setMessage_TYPE(ExcelUtil.formatCell(hssfRow.getCell(23)));
								sacch.setSourceData(ExcelUtil.formatCell(hssfRow.getCell(24)));
								/*
								sacch.setNCellLev1(ExcelUtil.formatCell(hssfRow.getCell(25)));
								sacch.setNCellLev2(ExcelUtil.formatCell(hssfRow.getCell(26)));
								sacch.setNCellLev3(ExcelUtil.formatCell(hssfRow.getCell(27)));
								sacch.setNCellLev4(ExcelUtil.formatCell(hssfRow.getCell(28)));
								sacch.setNCellLev5(ExcelUtil.formatCell(hssfRow.getCell(29)));
								sacch.setNCellLev6(ExcelUtil.formatCell(hssfRow.getCell(30)));
								sacch.setNCellLev7(ExcelUtil.formatCell(hssfRow.getCell(31)));
								sacch.setNCellLev8(ExcelUtil.formatCell(hssfRow.getCell(32)));
								*/
								sacch.setPowerLevel(ExcelUtil.formatCell(hssfRow.getCell(33)));
								sacch.setRX_LEV_Full(ExcelUtil.formatCell(hssfRow.getCell(34)));
								sacch.setRX_LEV_Sub(ExcelUtil.formatCell(hssfRow.getCell(35)));
								sacch.setRX_QUAL_Full(ExcelUtil.formatCell(hssfRow.getCell(36)));
								sacch.setRX_QUAL_Sub(ExcelUtil.formatCell(hssfRow.getCell(37)));
								sacch.setTA(ExcelUtil.formatCell(hssfRow.getCell(38)));
								sacch.setTS(ExcelUtil.formatCell(hssfRow.getCell(39)));
								sacch.setARFCNs(ExcelUtil.formatCell(hssfRow.getCell(40)));
								/*
								sacch.setNCBCCH_BISC1(ExcelUtil.formatCell(hssfRow.getCell(41)));
								sacch.setNCBCCH_BISC2(ExcelUtil.formatCell(hssfRow.getCell(42)));
								sacch.setNCBCCH_BISC3(ExcelUtil.formatCell(hssfRow.getCell(43)));
								sacch.setNCBCCH_BISC4(ExcelUtil.formatCell(hssfRow.getCell(44)));
								sacch.setNCBCCH_BISC5(ExcelUtil.formatCell(hssfRow.getCell(45)));
								sacch.setNCBCCH_BISC6(ExcelUtil.formatCell(hssfRow.getCell(46)));
								sacch.setNCBCCH_BISC7(ExcelUtil.formatCell(hssfRow.getCell(47)));
								sacch.setNCBCCH_BISC8(ExcelUtil.formatCell(hssfRow.getCell(48)));
								*/
								sacch.setFrame(ExcelUtil.formatCell(hssfRow.getCell(49)));
								sacch.setTimeSlot(ExcelUtil.formatCell(hssfRow.getCell(50)));
								sacch.setTMSI(ExcelUtil.formatCell(hssfRow.getCell(51)));
								sacch.setChannel(ExcelUtil.formatCell(hssfRow.getCell(52)));
								sacch.setTCH(ExcelUtil.formatCell(hssfRow.getCell(53)));
								sacch.setIMEI(ExcelUtil.formatCell(hssfRow.getCell(54)));
								sacch.setL2(ExcelUtil.formatCell(hssfRow.getCell(55)));
								sacch.setDECODED(ExcelUtil.formatCell(hssfRow.getCell(56)));

								map.put("table_name", "sacch_"+sacch.getFrontProcessorId());
								map.put("PrimaryKey", sacch.getPrimaryKey());
								map.put("TimeStamp", sacch.getTimeStamp());
//								map.put("StartTime", sacch.getStartTime());
								map.put("FrontProcessorId", sacch.getFrontProcessorId());
								map.put("RecordId", sacch.getRecordId());
								map.put("IMSI", sacch.getTMSI());
								map.put("LogicChannel", sacch.getLogicChannel());
								map.put("MSISDN", sacch.getMSISDN());
								map.put("Direction", sacch.getDirection());
								map.put("FrameNumber", sacch.getFrameNumber());
								map.put("SiteName", sacch.getSiteName());
								map.put("Decode", sacch.getDecode());
								map.put("RawMessage", sacch.getRawMessage());
								map.put("BCCH", sacch.getBCCH());
								map.put("NO_NCELL_M", sacch.getNO_NCELL_M());
								map.put("BCCH_FREQ_NCELL", sacch.getBCCH_FREQ_NCELL());
								map.put("Kilometer", sacch.getKilometer());
								map.put("Speed", sacch.getSpeed());
								map.put("CI", sacch.getCI());
								map.put("LAC", sacch.getLAC());
								map.put("CTCS_ID", sacch.getCTCS_ID());
								map.put("ENGINE_ID", sacch.getENGINE_ID());
								map.put("MS_TYPE", sacch.getMS_TYPE());
								map.put("Message_TYPE", sacch.getMessage_TYPE());
								map.put("SourceData", sacch.getSourceData());
								/*
								map.put("NCellLev1", sacch.getNCellLev1());
								map.put("NCellLev2", sacch.getNCellLev2());
								map.put("NCellLev3", sacch.getNCellLev3());
								map.put("NCellLev4", sacch.getNCellLev4());
								map.put("NCellLev5", sacch.getNCellLev5());
								map.put("NCellLev6", sacch.getNCellLev6());
								map.put("NCellLev7", sacch.getNCellLev7());
								map.put("NCellLev8", sacch.getNCellLev8());
								*/
								map.put("PowerLevel", sacch.getPowerLevel());
								map.put("RX_LEV_Full", sacch.getRX_LEV_Full());
								map.put("RX_LEV_Sub", sacch.getRX_LEV_Sub());
								map.put("RX_QUAL_Full", sacch.getRX_QUAL_Full());
								map.put("RX_QUAL_Sub", sacch.getRX_QUAL_Sub());
								map.put("TA", sacch.getTA());
								map.put("TS", sacch.getTS());
								map.put("ARFCNs", sacch.getARFCNs());
								/*
								map.put("NCBCCH_BISC1", sacch.getNCBCCH_BISC1());
								map.put("NCBCCH_BISC2", sacch.getNCBCCH_BISC2());
								map.put("NCBCCH_BISC3", sacch.getNCBCCH_BISC3());
								map.put("NCBCCH_BISC4", sacch.getNCBCCH_BISC4());
								map.put("NCBCCH_BISC5", sacch.getNCBCCH_BISC5());
								map.put("NCBCCH_BISC6", sacch.getNCBCCH_BISC6());
								map.put("NCBCCH_BISC7", sacch.getNCBCCH_BISC7());
								map.put("NCBCCH_BISC8", sacch.getNCBCCH_BISC8());
								*/
								map.put("FP_1000", sacch.getFP_1000());
								map.put("FP_1001", sacch.getFP_1001());
								map.put("FP_1002", sacch.getFP_1002());
								map.put("FP_1003", sacch.getFP_1003());
								map.put("FP_1004", sacch.getFP_1004());
								map.put("FP_1005", sacch.getFP_1005());
								map.put("FP_1006", sacch.getFP_1006());
								map.put("FP_1007", sacch.getFP_1007());
								map.put("FP_1008", sacch.getFP_1008());
								map.put("FP_1009", sacch.getFP_1009());
								map.put("FP_1010", sacch.getFP_1010());
								map.put("FP_1011", sacch.getFP_1011());
								map.put("FP_1012", sacch.getFP_1012());
								map.put("FP_1013", sacch.getFP_1013());
								map.put("FP_1014", sacch.getFP_1014());
								map.put("FP_1015", sacch.getFP_1015());
								map.put("FP_1016", sacch.getFP_1016());
								map.put("FP_1017", sacch.getFP_1017());
								map.put("FP_1018", sacch.getFP_1018());
								map.put("Frame", sacch.getFrame());
								map.put("TimeSlot", sacch.getTimeSlot());
								map.put("TMSI", sacch.getTMSI());
								map.put("Channel", sacch.getChannel());
								map.put("TCH", sacch.getTCH());
								map.put("IMEI", sacch.getIMEI());
								map.put("L2", sacch.getL2());
								map.put("DECODED", sacch.getDECODED());
								sacchService.add(map);
              				}
              			}
               }
           }
       }
       result.put("success", "true");
       } catch (Exception e) {
 			result.put("success", "false");
 			e.printStackTrace();
 		}
 		ResponseUtil.write(response, result);
		return null;
	}
	
	
	/**
	 * 查找District列表
	 */
	@RequestMapping("/sacchDistrictComboList")
	public String sacchDistrictComboList(HttpServletResponse response)throws Exception{
		Map<String,Object> map=new HashMap<String,Object>();
		JSONArray jsonArray=new JSONArray();
		JSONObject jsonObject=new JSONObject();
		//System.out.println("开始找sitename" );
		jsonObject.put("id", -1);
		jsonObject.put("name", "");
		jsonArray.add(jsonObject);
		map.put("table_name","bs_info");
		List<String> bs_names=sacchService.findDistricts(map);
		for(int i=0;i<bs_names.size();i++)
		{
			jsonObject.put("id", i);
			jsonObject.put("name",bs_names.get(i));
			jsonArray.add(jsonObject);
			
		}
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 查找Message_TYPE列表
	 */
	@RequestMapping("/sacchMessage_TYPEComboList")
	public String sacchMessage_TYPEComboList(HttpServletResponse response)throws Exception{
		JSONArray jsonArray=new JSONArray();
		Map<String,Object> map=new HashMap<String,Object>();
		List<String> table_names = sacchService.findTableNames(map);
		List<SACCH> sacchList = new ArrayList<SACCH>();
		if(table_names!=null){
			for(int i=0;i<table_names.size();i++){
				map.put("table_name", table_names.get(i));
				List<SACCH> sub_sacchList = sacchService.findMessage_TYPEs(map);
				if (sub_sacchList != null && sub_sacchList.size() > 0)
					sacchList.addAll(sub_sacchList);
			}
		}
		
		//过滤重复的Message_TYPE
		Map message_TYPE_map = new HashMap<>();
		for (int i = 0; i < sacchList.size(); i++) {
			message_TYPE_map.put(sacchList.get(i).getMessage_TYPE(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = message_TYPE_map.entrySet().iterator();
		sacchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			SACCH sacch = new SACCH();
			sacch.setMessage_TYPE(entry.getKey());
			sacchList.add(sacch);
		}
		
		JSONArray rows=JSONArray.fromObject(sacchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 查找前置机列表
	 */
	@RequestMapping("/sacchFrontProcessorIdComboList")
	public String sacchFrontProcessorIdComboList(HttpServletResponse response)throws Exception{
		Map<String,Object> map=new HashMap<String,Object>();
		JSONArray jsonArray=new JSONArray();
		JSONObject jsonObject=new JSONObject();
		jsonObject.put("id", -1);
		jsonObject.put("name", "");
		jsonArray.add(jsonObject);
		map.put("table_name","bs_info");
		List<String> FrontProcessorIds=sacchService.findFrontProcessorIds(map);
		for(int i=0;i<FrontProcessorIds.size();i++)
		{
			jsonObject.put("id", i);
			jsonObject.put("name",FrontProcessorIds.get(i));
			jsonArray.add(jsonObject);
		}
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 查找IMSI列表
	 */
	@RequestMapping("/sacchIMSIComboList")
	public String sacchIMSIComboList(HttpServletResponse response)throws Exception{
		JSONArray jsonArray=new JSONArray();
		Map<String,Object> map=new HashMap<String,Object>();
		List<String> table_names = sacchService.findTableNames(map);
		List<SACCH> sacchList = new ArrayList<SACCH>();
		if(table_names!=null){
			for(int i=0;i<table_names.size();i++){
				map.put("table_name", table_names.get(i));
				List<SACCH> sub_sacchList = sacchService.findIMSIs(map);
				sacchList.addAll(sub_sacchList);
			}
		}
		// 过滤重复的IMSI
		Map iMSI_map = new HashMap<>();
		for (int i = 0; i < sacchList.size(); i++) {
			iMSI_map.put(sacchList.get(i).getIMSI(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = iMSI_map.entrySet().iterator();
		sacchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			SACCH sacch = new SACCH();
			sacch.setIMSI(entry.getKey());
			sacchList.add(sacch);
		}
				
		JSONArray rows=JSONArray.fromObject(sacchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 查找MSISDN列表
	 */
	@RequestMapping("/sacchMSISDNComboList")
	public String sacchMSISDNComboList(HttpServletResponse response)throws Exception{
		JSONArray jsonArray=new JSONArray();
		Map<String,Object> map=new HashMap<String,Object>();
		List<String> table_names = sacchService.findTableNames(map);
		List<SACCH> sacchList = new ArrayList<SACCH>();
		if(table_names!=null){
			for(int i=0;i<table_names.size();i++){
				map.put("table_name", table_names.get(i));
				List<SACCH> sub_sacchList = sacchService.findMSISDNs(map);
				sacchList.addAll(sub_sacchList);
			}
		}

		// 过滤重复的MSISDN
		Map mSISDN_map = new HashMap<>();
		for (int i = 0; i < sacchList.size(); i++) {
			mSISDN_map.put(sacchList.get(i).getMSISDN(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = mSISDN_map.entrySet().iterator();
		sacchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			SACCH sacch = new SACCH();
			sacch.setMSISDN(entry.getKey());
			sacchList.add(sacch);
		}
				
		JSONArray rows=JSONArray.fromObject(sacchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 查找CTCS_ID列表
	 */
	@RequestMapping("/sacchCTCS_IDComboList")
	public String sacchCTCS_IDComboList(HttpServletResponse response)throws Exception{
		JSONArray jsonArray=new JSONArray();
		Map<String,Object> map=new HashMap<String,Object>();
		List<String> table_names = sacchService.findTableNames(map);
		List<SACCH> sacchList = new ArrayList<SACCH>();
		if(table_names!=null){
			for(int i=0;i<table_names.size();i++){
				map.put("table_name", table_names.get(i));
				List<SACCH> sub_sacchList = sacchService.findCTCS_IDs(map);
				sacchList.addAll(sub_sacchList);
			}
		}
		
		// 过滤重复的CTCS_ID
		Map cTCS_ID_map = new HashMap<>();
		for (int i = 0; i < sacchList.size(); i++) {
			cTCS_ID_map.put(sacchList.get(i).getCTCS_ID(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = cTCS_ID_map.entrySet().iterator();
		sacchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			SACCH sacch = new SACCH();
			sacch.setCTCS_ID(entry.getKey());
			sacchList.add(sacch);
		}
				
		JSONArray rows=JSONArray.fromObject(sacchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
		return null;
	}
	
	/**
	 * 对List中每个对象元素逆序排序
	 */
	private static void ListSort(List<SACCH> list) {
		
		if (list == null || list.size() < 2) return;
		
		Collections.sort(list, new Comparator<SACCH>() {
			@Override
			public int compare(SACCH sf1, SACCH sf2) {
				try {
					Long l1 = Long.parseLong(sf1.getTimeStamp());
					Long l2 = Long.parseLong(sf2.getTimeStamp());
					if (l1 < l2) {
						return -1;
					} else if (l1 > l2) {
						return 1;
					} else {
						return 0;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return 0;
			}
		});
	}
}